Skip Headers

Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next

# 66 DBMS_OUTLN

The `DBMS_OUTLN` package, synonymous with `OUTLN_PKG`, contains the functional interface for subprograms associated with the management of stor ed outlines.

See Also: For more information about using the `DBMS_OUTLN` package, see "Using Plan Stability" in Oracle Database Performance Tuning Guide.

This chapter contains the following topics:

- Using DBMS_OUTLN

  - Overview

  - Security Model

- Summary of DBMS_OUTLN Subprograms

## Using DBMS_OUTLN

- Overview

- Security Model

### Overview

A stored outline is the stored data that pertains to an execution plan for a given SQL statement. It enables the optimizer to repeatedly re-create execution plans that are equivalent to the plan originally generated along with the outline.The data stored in an outline consists, in part, of a set of hints that are used to achieve plan stability.

### Security Model

`DBMS_OUTLN` contains management procedures that should be available to appropriate users only. `EXECUTE` privilege is not extended to the general user community unless the DBA explicitly does so.

PL/SQL functions that are available for outline management purposes can be executed only by users with `EXECUTE` privilege on the procedure (or package).

## Summary of DBMS_OUTLN Subprograms

Table 66-1 DBMS_OUTLN Package Subprograms

Subprogram | Description
--- | ---
CLEAR_USED Procedure | Clears the outline 'used' flag
CREATE_OUTLINE Procedure | Generates outlines from the shared cursor identified by hash value and child number
DROP_BY_CAT Procedure | Drops outlines that belong to a specified category
DROP_UNUSED Procedure | Drops outlines that have never been applied in the compilation of a SQL statement
EXACT_TEXT_SIGNATURES Procedure | Updates outline signatures to those that compute based on exact text matching
UPDATE_BY_CAT Procedure | Changes the category of outlines in one category to a new category
UPDATE_SIGNATURES Procedure | Updates outline signatures to the current version's signature

### CLEAR_USED Procedure

This procedure clears the outline 'used' flag.

Syntax

    DBMS_OUTLN.CLEAR_USED (
       name    IN    VARCHAR2);

Parameters

Table 66-2 CLEAR_USED Procedure Parameters

Parameter | Description
--- | ---
`name` | Name of the outline.

### CREATE_OUTLINE Procedure

This procedure generates an outline from the shared cursor identified by hash value and child number.

Syntax

    DBMS_OUTLN.CREATE_OUTLINE (
       hash_value    IN NUMBER,
       child_number  IN NUMBER,
       category      IN VARCHAR2 DEFAULT 'DEFAULT');

Parameters

Table 66-3 CREATE_OUTLINE Procedure Parameters

Parameter | Description
--- | ---
`hash_value` | Hash value identifying the target shared cursor.
`child_number` | Child number of the target shared cursor.
`category` | Category in which to create outline (optional).

### DROP_BY_CAT Procedure

This procedure drops outlines that belong to a particular category. While outlines are put into the `DEFAULT` category unless otherwise specified, users have the option of grouping their outlines into groups called categories.

Syntax

    DBMS_OUTLN.DROP_BY_CAT (
       cat VARCHAR2);

Parameters

Table 66-4 DROP_BY_CAT Procedure Parameters

Parameter | Description
--- | ---
`cat` | Category of outlines to drop.

Usage Notes

This procedure purges a category of outlines in a single call.

Examples

This example drops all outlines in the `DEFAULT` category:

    DBMS_OUTLN.DROP_BY_CAT('DEFAULT');

### DROP_UNUSED Procedure

This procedure drops outlines that have never been applied in the compilation of a SQL statement.

Syntax

    DBMS_OUTLN.DROP_UNUSED;

Usage Notes

You can use `DROP_UNUSED` for outlines generated by an application for one-time use SQL statements created as a result of dynamic SQL. These outlines are never used and take up valuable disk space.

### EXACT_TEXT_SIGNATURES Procedure

This procedure updates outline signatures to those that compute based on exact text matching.

Syntax

    DBMS.OUTLN.EXACT_TEXT_SIGNATURES;

Usage Notes

This procedure is relevant only for downgrading an outline to 8.1.6 or earlier.

### UPDATE_BY_CAT Procedure

This procedure changes the category of all outlines in one category to a new category.

Syntax

    DBMS.OUTLN.UPDATE_BY_CAT (
       oldcat    VARCHAR2 default 'DEFAULT',
       newcat    VARCHAR2 default 'DEFAULT');

Parameters

Table 66-5 UPDATE_BY_CAT Procedure Parameters

Parameter | Description
--- | ---
`oldcat` | The current category of outlines.
`newcat` | The new category of outlines.

### UPDATE_SIGNATURES Procedure

This procedure updates outline signatures to the current version's signature.

Syntax

    DBMS.OUTLN.UPDATE_SIGNATURES;

Usage Notes

You should execute this procedure if you have imported outlines generated in an earlier release to ensure that the signatures are compatible with the current release's computation algorithm.